﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Windows.Forms;
using QuanLyHoSoCongChung.App_Code.Common.Utils;
using System.Xml;

namespace QuanLyHoSoCongChung.Libs
{
    class DataService
    {
        private static string strCnn="";
		private static OleDbConnection Cnn;

        /// 
        /// </summary>
        public DataService()
		{

		}

        /// <summary>
        /// 
        /// </summary>
		private string GetString()
		{
            string strKQ = string.Empty;
            try
            {
                XmlDocument Tai_lieu = Libs.DataServiceXML.Doc_XML("PathToDB.xml");
                XmlElement Goc = Tai_lieu.DocumentElement;
                strKQ = Goc.SelectSingleNode("connectionstring").InnerText;
            }
            catch (Exception ex)
            {
                ProjectUtils.ShowError(ex.Message);
            }
            return strKQ;
		}

        /// <summary>
        /// Đọc dữ liệu từ 1 table theo câu truy vấn
        /// </summary>
        /// <param name="strSQL"></param>
        /// <returns></returns>
		public DataTable Doc_Bang(string strSQL)
		{
            string strConnectionString = string.Empty;
            if (string.IsNullOrEmpty(strConnectionString))
                strConnectionString = GetString();
            DataTable dtb = new DataTable();
            try
			{
                OleDbConnection cnn = new OleDbConnection(strConnectionString);
				if  (cnn.State!=ConnectionState.Open)
				{				
					cnn.Open();					
				}
				OleDbDataAdapter  da=new OleDbDataAdapter (strSQL,cnn);
				da.Fill(dtb);
				cnn.Close();
			}
			catch (Exception ex)
			{
                ProjectUtils.ShowError(ex.Message);
			}
			return dtb;
		}


        /// <summary>
        /// 
        /// </summary>
        /// <param name="cmd"></param>
        /// <returns></returns>
		public static DataTable Doc_Bang(OleDbCommand cmd)
		{
			DataTable dt=new DataTable("bang");
			try
			{
				//if (strCnn=="")GetString();
				Cnn=new OleDbConnection(strCnn);
				cmd.Connection=Cnn;
				OleDbDataAdapter DA=new OleDbDataAdapter(cmd);
				DA.Fill(dt);
				
			}
			catch (System.Exception ex)
			{
                ProjectUtils.ShowError(ex.Message);
			}
			return dt;
		}

        /// <summary>
        /// Lấy hết dữ liệu từ một bảng.
        /// </summary>
        /// <param name="strTenBang"></param>
        /// <returns></returns>
        public DataTable DocHetBang(string strTenBang)
        {
            DataTable dtb = null;
            try
            {
                dtb = this.Doc_Bang("SELECT * FROM " + strTenBang);
                if (dtb.Rows.Count == 0)
                    ProjectUtils.ShowInfo("Không có dữ liệu");
            }
            catch (Exception ex)
            {
                ProjectUtils.ShowError(ex.Message);
            }
            return dtb;
        }

        /// <summary>
        /// Đọc cấu trúc bảng
        /// </summary>
        /// <param name="strTenBang"></param>
        /// <returns></returns>
        public DataTable DocCauTrucBang(string strTenBang, string strKhoaChinh)
        {
            DataTable dtb = null;
            try
            {
                string strSql = "SELECT * FROM " + strTenBang + " where " + strKhoaChinh + "= ''";
                dtb = this.Doc_Bang(strSql);
            }
            catch (Exception ex)
            {
                ProjectUtils.ShowError(ex.Message);
            }
            return dtb;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="cmd"></param>
        /// <returns></returns>
		public static int Cap_Nhat(OleDbCommand cmd)
		{
			int kq=0;
			//OleDbTransaction trans;
			try
			{
				//OleDbDataAdapter DA=new OleDbDataAdapter();
				Cnn=new OleDbConnection(strCnn);
				if  (Cnn.State!=ConnectionState.Open)Cnn.Open();
					
				//trans=Cnn.BeginTransaction();
				cmd.Connection=Cnn;
				//DA.UpdateCommand=cmd;
				
				
				kq=cmd.ExecuteNonQuery();
				Cnn.Close();
				//trans.Commit();
					
			}
			catch (System.Exception e)
			{
				//trans.Rollback();
				//Xu_Ly.XL_LOI.HienLoi(e);
			}
			
			return kq;
		}

		public static int Ghi_Bang (string TenBang, DataTable Bang)
		{
			string sql="select * from "+TenBang;
			int kq=0;
			try
			{
				Cnn= new OleDbConnection (strCnn);
				if  (Cnn.State!=ConnectionState.Open)
				{
					Cnn.Open();
				
				}
				OleDbDataAdapter  DA=new OleDbDataAdapter(sql,Cnn);
				OleDbCommandBuilder CB =new OleDbCommandBuilder(DA);
				
				kq=DA.Update(Bang);
				Cnn.Close();
			}
			catch(Exception EX)
			{
                ProjectUtils.ShowError(EX.Message);
			}
			return kq;
		}
    }
}
